package com.java.test.system.depositsettings;

import com.java.test.system.depositsettings.dto.DepositSettingsAddDTO;
import com.java.test.system.depositsettings.dto.DepositSettingsQueryDTO;
import com.java.test.system.depositsettings.dto.DepositSettingsUpdateDTO;
import com.java.test.system.depositsettings.entity.DepositSettingsEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

/**
 * TODO
 *
 * @author Administrator
 * @date 2020/6/29 0029
 */
@Component
public class DepositSettingsDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询
     * @param queryDTO
     * @author
     * @date 2020/7/15 0015 13:42
     * @return List<DepositSettingsEntity>
     */
    public List<DepositSettingsEntity> getSelectByList(DepositSettingsQueryDTO queryDTO) {
        String sql = "select * from sys_deposit_settings";

        List<DepositSettingsEntity> list = jdbcTemplate.query(sql,new DepositSettingsEntity());

        return list;
    }

    /**
     * 判断数据是否存在
     * @param addDTO
     * @author 
     * @date 2020/7/15 0015 13:43 
     * @return com.java.test.system.depositsettings.entity.DepositSettingsEntity
     */
    public DepositSettingsEntity getByGName(DepositSettingsAddDTO addDTO) {
        String sql = "select * from sys_deposit_settings where name =? and amountStart=? and amountEnd = ? and multiple=? and areaId=?";
        DepositSettingsEntity info = null;
        try {
            info= jdbcTemplate.queryForObject(sql, new DepositSettingsEntity(), addDTO.getName(),addDTO.getAmountStart(),addDTO.getAmountEnd(),addDTO.getMultiple(),addDTO.getAreaId());
        }catch (EmptyResultDataAccessException e){
            return null;
        }
        return info;
    }

    /**
     * 获取数据BYid
     * @param id
     * @return
     */
    public DepositSettingsEntity getById(String id) {
        String sql = "select *  from sys_deposit_settings where id = ?";
        DepositSettingsEntity info = null;
        try {
            info = jdbcTemplate.queryForObject(sql, new DepositSettingsEntity(), id);
        }catch (EmptyResultDataAccessException e){
            return null;
        }

        return info;
    }

    /**
     * 保存
     * @param addDTO
     * @author 
     * @date 2020/7/15 0015 13:44 
     * @return void
     */
    public void insertSave(DepositSettingsAddDTO addDTO) {
        String sql ="insert into sys_deposit_settings(name,amountStart,multiple,areaId,remarks,operation,createTime,status,amountEnd) " +
                "values (?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,addDTO.getName(),addDTO.getAmountStart(),addDTO.getMultiple(),
                addDTO.getAreaId(),addDTO.getRemarks(), addDTO.getOperation(),addDTO.getCreateTime(),addDTO.getStatus(),addDTO.getAmountEnd());
    }

    /**
     * 判断数据是否存在
     * @param updateDTO
     * @author 
     * @date 2020/7/15 0015 13:44 
     * @return com.java.test.system.depositsettings.entity.DepositSettingsEntity
     */
    public DepositSettingsEntity getByKeyExcludeId(DepositSettingsUpdateDTO updateDTO) {
        String sql = "select * from sys_deposit_settings where name =? and amountStart=? and amountEnd = ? and multiple=? and areaId=? and id <> ?";
        DepositSettingsEntity info = null;
        try {
            info = jdbcTemplate.queryForObject(sql, new DepositSettingsEntity(),updateDTO.getName(),updateDTO.getAmountStart(),updateDTO.getAmountEnd(),updateDTO.getMultiple(),
                    updateDTO.getAreaId(),updateDTO.getId());
        }catch (EmptyResultDataAccessException e){
            return null;
        }
        return info;
    }
    
    /**
     * 修改
     * @param updateDTO
     * @author 
     * @date 2020/7/15 0015 13:44 
     * @return void
     */
    public void updateById(DepositSettingsUpdateDTO updateDTO) {
        String sql = "update sys_deposit_settings " +
                "set name = ?,amountStart = ?,amountEnd = ?,multiple = ?,areaId = ?,modifyTime=?,status=?,remarks=? " +
                "where id = ?";

        jdbcTemplate.update(sql, updateDTO.getName(),updateDTO.getAmountStart(),updateDTO.getAmountEnd(),updateDTO.getMultiple(),
                updateDTO.getAreaId(),updateDTO.getModifyTime(),updateDTO.getStatus(),updateDTO.getRemarks(),updateDTO.getId());
    }

    /**
     * 删除
     * @param id
     * @author 
     * @date 2020/7/15 0015 13:45 
     * @return void
     */
    public void deleteById(String id) {
        String sql ="DELETE FROM sys_deposit_settings WHERE id = ?";
        jdbcTemplate.update(sql,id);
    }


    public List<DepositSettingsEntity> selectSettingByAmount(Double amount) {
        String sql = "SELECT * FROM `sys_deposit_settings` t  where ? BETWEEN t.amountStart AND t.amountEnd";
        List<DepositSettingsEntity> info = new ArrayList<>();
        try {
            info = jdbcTemplate.query(sql, new DepositSettingsEntity(),amount);
        }catch (EmptyResultDataAccessException e){
            e.printStackTrace();
        }
        return info;
    }
}
